// ============================================================================
// File: pkg/metadata/store.go - v2.0 enhanced
// ============================================================================
package metadata

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/containerd/containerd/snapshots"
	_ "github.com/mattn/go-sqlite3"
)

type Store struct {
	db *sql.DB
}

func NewStore(path string) (*Store, error) {
	db, err := sql.Open("sqlite3", path+"?_journal_mode=WAL")
	if err != nil {
		return nil, err
	}

	schema := `
	CREATE TABLE IF NOT EXISTS snapshots (
		id TEXT PRIMARY KEY,
		name TEXT UNIQUE,
		parent TEXT,
		kind INTEGER,
		created_at INTEGER,
		updated_at INTEGER,
		size INTEGER,
		inodes INTEGER,
		labels TEXT
	);
	CREATE INDEX IF NOT EXISTS idx_name ON snapshots(name);
	CREATE INDEX IF NOT EXISTS idx_parent ON snapshots(parent);
	CREATE INDEX IF NOT EXISTS idx_kind ON snapshots(kind);
	`

	if _, err := db.Exec(schema); err != nil {
		return nil, fmt.Errorf("failed to create schema: %w", err)
	}

	return &Store{db: db}, nil
}

func (s *Store) Create(ctx context.Context, kind snapshots.Kind, key, parent string, opts ...snapshots.Opt) (snapshots.Info, error) {
	now := time.Now()
	id := fmt.Sprintf("%d-%s", now.UnixNano(), key)

	info := snapshots.Info{
		Name:    key,
		Parent:  parent,
		Kind:    kind,
		Created: now,
		Updated: now,
		Labels:  make(map[string]string),
	}

	for _, opt := range opts {
		if err := opt(&info); err != nil {
			return snapshots.Info{}, err
		}
	}

	// Execute the insert statement with the correct fields
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO snapshots (id, name, parent, kind, created_at, updated_at, size, inodes, labels) 
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		id, info.Name, info.Parent, info.Kind, 
		info.Created.Unix(), info.Updated.Unix(), 0, 0, serializeLabels(info.Labels))

	return info, err
}

func (s *Store) Get(ctx context.Context, key string) (snapshots.Info, error) {
	var name, parent string
	var kind snapshots.Kind
	var createdAt, updatedAt int64
	var labels string

	err := s.db.QueryRowContext(ctx,
		`SELECT name, parent, kind, created_at, updated_at, labels 
		 FROM snapshots WHERE name = ?`, key).
		Scan(&name, &parent, &kind, 
			&createdAt, &updatedAt, &labels)

	if err == sql.ErrNoRows {
		return snapshots.Info{}, fmt.Errorf("snapshot %s: not found", key)
	}
	if err != nil {
		return snapshots.Info{}, err
	}

	info := snapshots.Info{
		Name:    name,
		Parent:  parent,
		Kind:    kind,
		Created: time.Unix(createdAt, 0),
		Updated: time.Unix(updatedAt, 0),
		Labels:  deserializeLabels(labels),
	}

	return info, nil
}

func (s *Store) Update(ctx context.Context, info snapshots.Info, fieldpaths ...string) (snapshots.Info, error) {
	info.Updated = time.Now()
	_, err := s.db.ExecContext(ctx,
		`UPDATE snapshots SET labels = ?, updated_at = ? WHERE name = ?`,
		serializeLabels(info.Labels), info.Updated.Unix(), info.Name)

	return info, err
}

func (s *Store) Commit(ctx context.Context, key, name string, opts ...snapshots.Opt) error {
	now := time.Now()
	_, err := s.db.ExecContext(ctx,
		`UPDATE snapshots SET name = ?, kind = ?, updated_at = ? WHERE name = ?`,
		name, snapshots.KindCommitted, now.Unix(), key)
	return err
}

func (s *Store) Remove(ctx context.Context, key string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM snapshots WHERE name = ?`, key)
	return err
}

func (s *Store) Walk(ctx context.Context, fn snapshots.WalkFunc, filters ...string) error {
	query := `SELECT name, parent, kind, created_at, updated_at, labels FROM snapshots`
	
	if len(filters) > 0 {
		query += " WHERE " + strings.Join(filters, " AND ")
	}

	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var name, parent, labels string
		var kind snapshots.Kind
		var createdAt, updatedAt int64
		
		if err := rows.Scan(&name, &parent, &kind, &createdAt, &updatedAt, &labels); err != nil {
			return err
		}
		
		info := snapshots.Info{
			Name:    name,
			Parent:  parent,
			Kind:    kind,
			Created: time.Unix(createdAt, 0),
			Updated: time.Unix(updatedAt, 0),
			Labels:  deserializeLabels(labels),
		}

		if err := fn(ctx, info); err != nil {
			return err
		}
	}

	return rows.Err()
}

func (s *Store) Close() error {
	return s.db.Close()
}

func serializeLabels(labels map[string]string) string {
	if len(labels) == 0 {
		return ""
	}
	var parts []string
	for k, v := range labels {
		parts = append(parts, fmt.Sprintf("%s=%s", k, v))
	}
	return strings.Join(parts, ";")
}

func deserializeLabels(s string) map[string]string {
	labels := make(map[string]string)
	if s == "" {
		return labels
	}
	for _, part := range strings.Split(s, ";") {
		kv := strings.SplitN(part, "=", 2)
		if len(kv) == 2 {
			labels[kv[0]] = kv[1]
		}
	}
	return labels
}